with f as (
  select
      id
     ,appeal_no
     ,waybill_no
     ,user_name
     ,phone
     ,email
     ,status
     ,type
     ,cause
     ,appeal_time
     ,province_id
     ,province_desc
     ,city_id
     ,city_desc
     ,description
     ,trace
     ,evidence
     ,is_insured
     ,insured_value
     ,acceptance_office_province_id
     ,acceptance_office_province_desc
     ,acceptance_office_city_id
     ,acceptance_office_city_desc
     ,acceptance_office_time
     ,valid
     ,economic_loss
     ,discontact_count
     ,complaints_result
     ,remark
     ,complain_info
     ,express_time
     ,close_time
     ,close_office_province_id
     ,close_office_province_desc
     ,close_office_city_id
     ,close_office_city_desc
     ,blame_province_id
     ,blame_province_desc
     ,blame_city_id
     ,blame_city_desc
     ,sender_name
     ,sender_phone
     ,sender_address
     ,send_network_id
     ,send_network_code
     ,send_network_name
     ,receiver_name
     ,receiver_phone
     ,receiver_address
     ,dispatch_network_id
     ,dispatch_network_code
     ,dispatch_network_name
     ,trans_count
     ,trans_reason
     ,trans_time
     ,accept_network_id
     ,accept_network_code
     ,accept_network_name
     ,accept_time
     ,assign_time
     ,assign_type
     ,accept_user_id
     ,accept_user_code
     ,accept_user_name
     ,blame_network_id
     ,blame_network_code
     ,blame_network_name
     ,blame_proxy_id
     ,blame_proxy_code
     ,blame_proxy_name
     ,is_upgrade
     ,upgrade_network_id
     ,upgrade_network_code
     ,upgrade_network_name
     ,upgrade_time
     ,process_user_id
     ,process_user_code
     ,process_user_name
     ,process_time
     ,create_time
     ,update_time
     ,audit_opinion
     ,audit_user_id
     ,audit_user_code
     ,audit_user_name
     ,accept_network_type
     ,accept_proxy_id
     ,accept_proxy_code
     ,accept_proxy_name
     ,accept_franchisee_id
     ,accept_franchisee_code
     ,accept_franchisee_name
     ,monitoring_time
     ,process_duration
     ,response_duration
     ,is_monitor_history
     ,is_repeat
     ,row_number() over(partition by waybill_no order by update_time desc) as rank
  from jms_ods.appeal_info
    where dt between date_sub('{{ execution_date | cst_ds }}',60)
         and date_add('{{ execution_date | cst_ds }}',60)
             and date(create_time) between date_sub('{{ execution_date | cst_ds }}',60)
             and '{{ execution_date | cst_ds }}'
)
insert overwrite table jms_dwd.dwd_appeal_info_dt partition(dt)
  select
      id
     ,appeal_no
     ,waybill_no
     ,user_name
     ,phone
     ,email
     ,status
     ,type
     ,cause
     ,appeal_time
     ,province_id
     ,province_desc
     ,city_id
     ,city_desc
     ,description
     ,trace
     ,evidence
     ,is_insured
     ,insured_value
     ,acceptance_office_province_id
     ,acceptance_office_province_desc
     ,acceptance_office_city_id
     ,acceptance_office_city_desc
     ,acceptance_office_time
     ,valid
     ,economic_loss
     ,discontact_count
     ,complaints_result
     ,remark
     ,complain_info
     ,express_time
     ,close_time
     ,close_office_province_id
     ,close_office_province_desc
     ,close_office_city_id
     ,close_office_city_desc
     ,blame_province_id
     ,blame_province_desc
     ,blame_city_id
     ,blame_city_desc
     ,sender_name
     ,sender_phone
     ,sender_address
     ,send_network_id
     ,send_network_code
     ,send_network_name
     ,receiver_name
     ,receiver_phone
     ,receiver_address
     ,dispatch_network_id
     ,dispatch_network_code
     ,dispatch_network_name
     ,trans_count
     ,trans_reason
     ,trans_time
     ,accept_network_id
     ,accept_network_code
     ,accept_network_name
     ,accept_time
     ,assign_time
     ,assign_type
     ,accept_user_id
     ,accept_user_code
     ,accept_user_name
     ,blame_network_id
     ,blame_network_code
     ,blame_network_name
     ,blame_proxy_id
     ,blame_proxy_code
     ,blame_proxy_name
     ,is_upgrade
     ,upgrade_network_id
     ,upgrade_network_code
     ,upgrade_network_name
     ,upgrade_time
     ,process_user_id
     ,process_user_code
     ,process_user_name
     ,process_time
     ,create_time
     ,update_time
     ,audit_opinion
     ,audit_user_id
     ,audit_user_code
     ,audit_user_name
     ,accept_network_type
     ,accept_proxy_id
     ,accept_proxy_code
     ,accept_proxy_name
     ,accept_franchisee_id
     ,accept_franchisee_code
     ,accept_franchisee_name
     ,monitoring_time
     ,process_duration
     ,response_duration
     ,is_monitor_history
     ,is_repeat
     ,date(create_time) as dt
 from f
   where rank = 1
          distribute by 1 ;
